package cn.com.generaldata.os.service.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.nutz.dao.Cnd;
import org.nutz.dao.ConnCallback;
import org.nutz.dao.Dao;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.lang.Lang;
import org.nutz.lang.Times;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.generaldata.jsme_indicator_system.entity.UnitNz;
import cn.com.generaldata.jsme_indicator_system.util.Constants;
import cn.com.generaldata.os.constant.ReportConstant;
import cn.com.generaldata.os.dao.intf.BaseDAOInterface;
import cn.com.generaldata.os.pojo.AreaPerform;
import cn.com.generaldata.os.service.common.AreaService;

/**
 * 报表指标查询service.
 */
@Service
public class IndicatorWordService {

	private static final Logger logger = LoggerFactory.getLogger(IndicatorWordService.class);

	private static final String AREA_ID_KEY = "areaId";
	private static final String AREA_NAME_KEY = "地区名称";
	@Autowired
	private Dao dao;

	@Autowired
	private SqlTemplate sqlTemplate;
	@Autowired
	private AreaService areaService;
	private BaseDAOInterface<AreaPerform> areaPerformDAO;

	public void setAreaPerformDAO(BaseDAOInterface<AreaPerform> areaPerformDAO) {
		this.areaPerformDAO = areaPerformDAO;
	}

	@Autowired
	private UnitService unitService;

	private final Map<Integer, Integer> monthQuarterMap = new HashMap<Integer, Integer>() {
		{
			put(1, 1);
			put(2, 1);
			put(3, 1);
			put(4, 2);
			put(5, 2);
			put(6, 2);
			put(7, 3);
			put(8, 3);
			put(9, 3);
			put(10, 4);
			put(11, 4);
			put(12, 4);
		}
	};

	/**
	 * 导出Word.
	 * 
	 * @param timeType
	 *            报表类型
	 * @param year
	 *            年份
	 * @param quarter
	 *            季度
	 * @param month
	 *            月份
	 * @param folderPath
	 *            模板路径
	 * @param numPoint
	 *            数值小数点位数
	 * @param persentPoint
	 *            百分比小数点位数
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public HWPFDocument exportWordReport(int timeType, int year, int quarter, int month, String folderPath,
			int numPoint, int persentPoint) throws FileNotFoundException, IOException {

		boolean oldSystem = true;

		// 时间参数
		ReportTimeVO timeVo = new ReportTimeVO();
		timeVo.setTimeType(timeType); // 年报，季报，月报

		// 新系统，直接timeType=3的是半年报，yearTime字段不使用
		// JSP页面，半年报是按3传的
		// 旧系统，timeType=1 && yearTime=2 是全年报，timeType=1 && yearTime=1 是半年报
		if (oldSystem) {
			if (timeType == Constants.REPORT_TIME_HALF_YEAR) {
				timeVo.setTimeType(Constants.REPORT_TIME_YEAR); // 年报
				timeVo.setYearTime(Constants.REPORT_HALF_YEAR); // 半年报
			} else if (timeType == Constants.REPORT_TIME_YEAR) {
				timeVo.setTimeType(Constants.REPORT_TIME_YEAR); // 年报
				timeVo.setYearTime(Constants.REPORT_FULL_YEAR); // 年报
			}
		}
		timeVo.setYear(year); // 年份
		timeVo.setQuarter(quarter); // 季度
		timeVo.setMonth(month); // 月份
		Date versionDate = timeVo.toReportVersionDate();

		// 上期的时间参数
		ReportTimeVO timeVoLast = timeVo.clone();
		timeVoLast.setYear(year - 1);
		Date versionDateLast = timeVoLast.toReportVersionDate();

		// 模板文件名
		String fileName = this.getWordTemplateFileName(timeType);

		// 打开模板
		String filePath = folderPath + fileName;
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HWPFDocument doc = new HWPFDocument(fs);

		// 位置Map，指标数值倍数Map，值Map
		Map<String, String> locationMap = new HashMap<String, String>();
		Map<String, Integer> divMap = new HashMap<String, Integer>();
		Map<String, String> valueMap = new HashMap<String, String>();

		// 位置Map，指标数值倍数Map，值Map
		Map<String, String> locationMapLast = new HashMap<String, String>();
		Map<String, Integer> divMapLast = new HashMap<String, Integer>();
		Map<String, String> valueMapLast = new HashMap<String, String>();

		// 数值格式化
		NumberFormat nf = NumberFormat.getInstance();
		nf.setMaximumFractionDigits(numPoint); // 小数点最大位数
		nf.setMinimumFractionDigits(0);

		// 百分比格式化
		NumberFormat pf = NumberFormat.getInstance();
		pf.setMaximumFractionDigits(persentPoint); // 小数点最大位数
		pf.setMinimumFractionDigits(0);

		// 价格格式化
		NumberFormat priceFormat = NumberFormat.getInstance();
		priceFormat.setMaximumFractionDigits(2); // 小数点最大位数
		priceFormat.setMinimumFractionDigits(0);

		// ------------------替换年份---------------------------
		doc.getRange().replaceText("${年份}", "" + timeVo.getYear());
		doc.getRange().replaceText("${月份}", "" + timeVo.getMonth());
		doc.getRange().replaceText("${季度}", "" + timeVo.getQuarter());

		// ------------------替换本期指标的值---------------------------
		// 格式： ${山东省渔业生产基本情况_水产品总产量_数量_年报#10000}
		// 最后的#10000，表示要显示成"xx万"的形式，所以数值要除以10000，不注明倍数，默认是1
		String text = doc.getRange().text();
		String regex = "\\$\\{(.*?)(#(\\d+))?}";
		Matcher m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {

			// 指标名称：山东省渔业生产基本情况_水产品总产量_数量_年报
			String matchStr = m.group(0);
			String indName = m.group(1);
			int toDiv = 1; // 倍数默认是1
			if (m.group(3) != null) {
				toDiv = Integer.valueOf(m.group(3));
			}

			// 取本期的值
			double result = this.getIndValueXX(indName, timeVo, versionDate, toDiv, locationMap, divMap, valueMap);

			// 替换内容
			doc.getRange().replaceText(matchStr, nf.format(result));
		}

		// -----------------计算公式：求和----------------------------
		// 求和：格式如$SUM(0.3303+8.7306)
		text = doc.getRange().text();
		regex = "\\$SUM\\((.*?)\\)";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {
			String[] strArray = m.group(1).split("\\+");
			double result = 0.0d;
			for (String str : strArray) {
				result += Double.valueOf(str);
			}

			// 替换内容
			doc.getRange().replaceText(m.group(0), nf.format(result));
		}

		// -----------------计算公式：百分比----------------------------
		// 计算百分比，格式如：$P(13.9308/841.884)
		text = doc.getRange().text();
		regex = "\\$P\\(([\\d.]+)/([\\d.]+)\\)";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {
			double result = 0;
			double num = Double.valueOf(m.group(2));
			if (num > 0) {
				result = Double.valueOf(m.group(1)) / num * 100;
			}

			// 替换内容
			doc.getRange().replaceText(m.group(0), pf.format(result));
		}

		// -------------------------------------
		// 计算同期增长百分比，格式如：$TBP{山东省渔业生产基本情况_淡水捕捞_数量_年报+山东省渔业生产基本情况_淡水养殖_数量_年报}
		// 多个指标名，则是求和，然后看同比增长
		text = doc.getRange().text();
		regex = "\\$TBP\\{(.*?)}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {

			// 指标名称：山东省渔业生产基本情况_淡水捕捞_数量_年报
			String matchStr = m.group(0);
			String indNameStr = m.group(1);
			String[] indNameArray = indNameStr.split("\\+");
			int toDiv = 1;

			// 求和
			double allCurrent = 0.0d;
			double allLast = 0.0d;
			for (String indName : indNameArray) {

				// 取本期的值
				double resultCurrent = this.getIndValueXX(indName, timeVo, versionDate, toDiv, locationMap, divMap,
						valueMap);
				allCurrent += resultCurrent;

				// 取上期的值
				double resultLast = this.getIndValueXX(indName, timeVoLast, versionDateLast, toDiv, locationMapLast,
						divMapLast, valueMapLast);
				allLast += resultLast;

			}
			double result = 0;
			if (allLast > 0) {
				result = (allCurrent - allLast) / allLast * 100;
			}
			// 替换内容
			doc.getRange().replaceText(matchStr, pf.format(result));
		}

		// -------------------------------------
		// 计算同期增长值，格式如：$TBV{山东省渔业生产基本情况_淡水捕捞_数量_年报#10000}
		text = doc.getRange().text();
		regex = "\\$TBV\\{(.*?)(#(\\d+))?}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {

			// 指标名称：山东省渔业生产基本情况_淡水捕捞_数量_年报
			String matchStr = m.group(0);
			String indName = m.group(1);
			int toDiv = 1;
			if (m.group(3) != null) {
				toDiv = Integer.valueOf(m.group(3));
			}

			// 取本期的值
			double resultCurrent = this.getIndValueXX(indName, timeVo, versionDate, toDiv, locationMap, divMap,
					valueMap);

			// 取上期的值
			double resultLast = this.getIndValueXX(indName, timeVoLast, versionDateLast, toDiv, locationMapLast,
					divMapLast, valueMapLast);

			// 差值
			double result = resultCurrent - resultLast;

			// 替换内容
			doc.getRange().replaceText(matchStr, nf.format(result));
		}

		// -----------price使用，每个月有一套map--------------
		// 位置Map，指标数值倍数Map，值Map
		Map[] locationMapArray = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			locationMapArray[i] = new HashMap<String, String>();
		}
		Map[] divMapArray = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			divMapArray[i] = new HashMap<String, Integer>();
		}

		Map[] valueMapArray = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			valueMapArray[i] = new HashMap<String, String>();
		}

		// -----------price使用，每个月有一套map-------Last-------
		// 位置Map，指标数值倍数Map，值Map
		Map[] locationMapArrayLast = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			locationMapArrayLast[i] = new HashMap<String, String>();
		}
		Map[] divMapArrayLast = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			divMapArrayLast[i] = new HashMap<String, Integer>();
		}

		Map[] valueMapArrayLast = new HashMap[12];
		for (int i = 0; i < 12; i++) {
			valueMapArrayLast[i] = new HashMap<String, String>();
		}

		// 年报、半年报，季报使用PRICE标签
		// 计算价格的开始、结束月份
		int fromMonth = 0;
		int toMonth = 0;
		if (timeType == Constants.REPORT_TIME_YEAR) {
			fromMonth = 1;
			toMonth = 12;
		} else if (timeType == Constants.REPORT_TIME_HALF_YEAR) {
			fromMonth = 1;
			toMonth = 6;
		} else if (timeType == Constants.REPORT_TIME_QUARTER) {
			fromMonth = (quarter - 1) * 3 + 1;
			toMonth = (quarter - 1) * 3 + 3;
		}

		// --------------年报使用PRICE标签，月报不需要，月报里价格只是一个普通指标---------------
		// 计算水产品价格，格式如：$PRICE{山东省渔业统计月报表_淡水养殖产量_乌鳢_单价_月报}
		// 价格按xx元/公斤，DB取出直接显示，不进行单位转换。
		text = doc.getRange().text();
		regex = "\\$PRICE\\{(.*?)}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {

			// 指标名称：山东省渔业统计月报表_淡水养殖产量_乌鳢_单价_月报
			String matchStr = m.group(0);
			String indName = m.group(1);
			int toDiv = 1;

			// 12个月分别取值，然后取平均值
			double sum = 0.0d;
			ReportTimeVO time = timeVo.clone();
			time.setTimeType(Constants.REPORT_TIME_MONTH); // 月报
			for (int i = fromMonth; i <= toMonth; i++) {

				// 设置月份
				time.setMonth(i);

				// 取该月份的值
				double result = this.getIndValueXX(indName, time, versionDate, toDiv, locationMapArray[i - 1],
						divMapArray[i - 1], valueMapArray[i - 1]);
				sum += result;
			}
			double result = sum / 12;

			// 替换内容
			doc.getRange().replaceText(matchStr, priceFormat.format(result));
		}

		// ----------------水产品价格，同比增长%----
		// 计算水产品价格，格式如：$PRICE_P{山东省渔业统计月报表_淡水养殖产量_乌鳢_单价_月报}
		text = doc.getRange().text();
		regex = "\\$PRICE_P\\{(.*?)}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {

			// 指标名称：山东省渔业统计月报表_淡水养殖产量_乌鳢_单价_月报
			String matchStr = m.group(0);
			String indName = m.group(1);
			int toDiv = 1;

			// 12个月分别取值，然后取平均值
			double sum = 0.0d;
			ReportTimeVO time = timeVo.clone();
			time.setTimeType(Constants.REPORT_TIME_MONTH); // 月报
			for (int i = fromMonth; i <= toMonth; i++) {

				// 设置月份
				time.setMonth(i);

				// 取该月份的值
				double resultOne = this.getIndValueXX(indName, time, versionDate, toDiv, locationMapArray[i - 1],
						divMapArray[i - 1], valueMapArray[i - 1]);
				sum += resultOne;
			}
			double resultCurrent = sum / 12;

			// ----------同期值----------
			// 12个月分别取值，然后取平均值
			double sumLast = 0.0d;
			ReportTimeVO timeLast = timeVo.clone();
			timeLast.setYear(timeVo.getYear() - 1); // 去年
			timeLast.setTimeType(Constants.REPORT_TIME_MONTH); // 月报
			for (int i = fromMonth; i <= toMonth; i++) {

				// 设置月份
				time.setMonth(i);

				// 取该月份的值
				double resultOne = this.getIndValueXX(indName, timeLast, timeLast.toReportVersionDate(), toDiv,
						locationMapArrayLast[i - 1], divMapArrayLast[i - 1], valueMapArrayLast[i - 1]);
				sumLast += resultOne;
			}
			double resultLast = sumLast / 12;

			// 计算增长百分比
			double result = 0;
			if (resultLast > 0) {
				result = (resultCurrent - resultLast) / resultLast * 100;
			}

			// 替换内容
			doc.getRange().replaceText(matchStr, pf.format(result));
		}

		// ----------------特殊处理，FUCK-------------------
		// $CND{山东省淡水养殖产量(按品种分)_*_产量_年报,20,30}
		// 同比增长在20%~30%之间的，显示名字，FUCK~
		text = doc.getRange().text();
		regex = "\\$CND\\{(.*?),([\\d.]+)(,([\\d.]+))?}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {
			String matchStr = m.group(0);
			String indNameCnd = m.group(1);
			String minP = m.group(2); // 百分比min
			String maxP = m.group(4); // 百分比max

			// 山东省淡水养殖产量(按品种分)_*_产量_年报 -->山东省淡水养殖产量\(按品种分)_.*?_产量_年报
			// 转为正则表达式,括号需要转义
			String nameRegex = indNameCnd.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)").replace("*", "(.*?)");

			// 从locationMap里取出指标，看是否匹配这个正则，然后挨个取值判断
			// 认为之前，该表数据已经取过了~~
			List<String> resultList = new ArrayList<String>();
			for (Entry<String, String> entry : locationMap.entrySet()) {
				if (entry.getKey().matches(nameRegex)) {
					String indName = entry.getKey();
					int toDiv = 1;
					String location = entry.getValue(); // 位置
					String value = valueMap.get(location); // 值
					if (value == null || value.trim().length() == 0) {
						// 认为是0，那么铁定增长率不会>20%
						continue;
					}

					// 倍数按1算
					Integer div = divMap.get(indName);
					double resultCurrent = Double.valueOf(value.trim()) * div / toDiv;

					// 上期值
					location = locationMapLast.get(indName);
					value = valueMapLast.get(location); // 值
					if (value == null || value.trim().length() == 0) {
						// 认为是0，没法算增长率
						continue;
					}

					// 倍数按1算
					div = divMapLast.get(indName);
					double resultLast = Double.valueOf(value.trim()) * div / toDiv;
					if (resultLast == 0) {
						continue;
					}

					// 计算百分比
					double result = (resultCurrent - resultLast) / resultLast * 100;
					if (maxP == null) {
						if (result >= Integer.valueOf(minP)) {
							// 截取短名称
							Matcher mm = Pattern.compile(nameRegex).matcher(indName);
							mm.find();
							resultList.add(mm.group(1));
						}
					} else {
						if (result >= Integer.valueOf(minP) && result < Integer.valueOf(maxP)) {
							Matcher mm = Pattern.compile(nameRegex).matcher(indName);
							mm.find();
							resultList.add(mm.group(1));
						}
					}

				}
			}

			if (resultList.size() > 0) {

				// 最多显示5个
				if (resultList.size() > 5) {
					resultList = resultList.subList(0, 5);
				}

				// 拼接：A、B、C
				StringBuilder sb = new StringBuilder();
				for (String str : resultList) {
					sb.append(str).append("、");
				}
				sb.deleteCharAt(sb.length() - 1);

				// 替换内容
				doc.getRange().replaceText(matchStr, sb.toString());
			} else {
				// 替换内容
				doc.getRange().replaceText(matchStr, "");
			}
		}

		// ----------------特殊处理，FUCK-----FUCK------FUCK----FUCK----
		// $CND_PRICE{山东省渔业统计月报表_淡水养殖产量_*_单价_月报,20,30}
		// 同比增长在20%~30%之间的，显示名字，FUCK~
		text = doc.getRange().text();
		regex = "\\$CND_PRICE\\{(.*?),([\\d.]+)(,([\\d.]+))?}";
		m = Pattern.compile(regex, Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(text);
		while (m.find()) {
			String matchStr = m.group(0);
			String indNameCnd = m.group(1);
			String minP = m.group(2); // 百分比min
			String maxP = m.group(4); // 百分比max

			// 山东省渔业统计月报表_淡水养殖产量_*_单价_月报 -->山东省渔业统计月报表_淡水养殖产量_.*?_单价_月报
			// 转为正则表达式,括号需要转义
			String nameRegex = indNameCnd.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)").replace("*", "(.*?)");

			// 从locationMap里取出指标，看是否匹配这个正则，然后挨个取值判断
			// 认为之前，该表数据已经取过了~~
			List<String> resultList = new ArrayList<String>();

			// 价格没在主map里，在每月的map里
			HashMap<String, String> one = (HashMap<String, String>) locationMapArray[fromMonth - 1];
			for (Entry<String, String> entry : one.entrySet()) {
				if (entry.getKey().matches(nameRegex)) {
					String indName = entry.getKey();
					int toDiv = 1;
					String location = entry.getValue(); // 位置

					// 12个月分别取值，然后取平均值
					double sum = 0.0d;
					ReportTimeVO time = timeVo.clone();
					time.setTimeType(Constants.REPORT_TIME_MONTH); // 月报
					for (int i = fromMonth; i <= toMonth; i++) {

						// 设置月份
						time.setMonth(i);

						// 取该月份的值
						double resultOne = this.getIndValueXX(indName, time, versionDate, toDiv,
								locationMapArray[i - 1], divMapArray[i - 1], valueMapArray[i - 1]);
						sum += resultOne;
					}
					double resultCurrent = sum / 12;

					// ----------同期值----------
					// 12个月分别取值，然后取平均值
					double sumLast = 0.0d;
					ReportTimeVO timeLast = timeVo.clone();
					timeLast.setYear(timeVo.getYear() - 1); // 去年
					timeLast.setTimeType(Constants.REPORT_TIME_MONTH); // 月报
					for (int i = fromMonth; i <= toMonth; i++) {

						// 设置月份
						time.setMonth(i);

						// 取该月份的值
						double resultOne = this.getIndValueXX(indName, timeLast, timeLast.toReportVersionDate(), toDiv,
								locationMapArrayLast[i - 1], divMapArrayLast[i - 1], valueMapArrayLast[i - 1]);
						sumLast += resultOne;
					}
					double resultLast = sumLast / 12;

					// 计算增长百分比
					double result = 0;
					if (resultLast > 0) {
						result = (resultCurrent - resultLast) / resultLast * 100;
					}
					if (maxP == null) {
						if (result >= Integer.valueOf(minP)) {
							// 截取短名称
							Matcher mm = Pattern.compile(nameRegex).matcher(indName);
							mm.find();
							resultList.add(mm.group(1));
						}
					} else {
						if (result >= Integer.valueOf(minP) && result < Integer.valueOf(maxP)) {
							Matcher mm = Pattern.compile(nameRegex).matcher(indName);
							mm.find();
							resultList.add(mm.group(1));
						}
					}

				}
			}

			if (resultList.size() > 0) {

				// 最多显示5个
				if (resultList.size() > 5) {
					resultList = resultList.subList(0, 5);
				}

				// 拼接：A、B、C
				StringBuilder sb = new StringBuilder();
				for (String str : resultList) {
					sb.append(str).append("、");
				}
				sb.deleteCharAt(sb.length() - 1);

				// 替换内容
				doc.getRange().replaceText(matchStr, sb.toString());
			} else {
				// 替换内容
				doc.getRange().replaceText(matchStr, "");
			}
		}

		return doc;

	}

	/**
	 * 取指标的值.
	 * 
	 * @param indName
	 *            指标名称
	 * @param timeVo
	 *            时间参数
	 * @param versionDate
	 * @param toDiv
	 *            值除以的倍数
	 * @param locationMap
	 *            指标位置Map
	 * @param divMap
	 *            倍数Map
	 * @param valueMap
	 *            值Map
	 * @return
	 */
	private double getIndValueXX(String indName, ReportTimeVO timeVo, Date versionDate, int toDiv,
			Map<String, String> locationMap, Map<String, Integer> divMap, Map<String, String> valueMap) {
		// 报表类型名称（价格的报表名好像多几个字，可能搞不出来，呵呵）
		String typeName = indName.substring(0, indName.indexOf("_"));

		// 取位置
		if (!locationMap.containsKey(indName)) {
			this.getIndLocationFromDB(versionDate, typeName, locationMap, divMap);
		}
		String location = locationMap.get(indName);

		// 取值
		if (location != null) {
			if (!valueMap.containsKey(location)) {
				this.getIndValueFromDB(timeVo, typeName, valueMap);
			}
			String value = valueMap.get(location);
			if (value == null || value.trim().length() == 0) {
				return 0.0d;
			}

			Integer div = divMap.get(indName);
			double result = Double.valueOf(value.trim()) * div / toDiv;
			return result;
		}
		return 0.0d;
	}

	/**
	 * 从DB取得指标在报表中的行列位置.
	 * 
	 * @param versionDate
	 * @param typeName
	 * @param locationMap
	 * @param divMap
	 */
	private void getIndLocationFromDB(final Date versionDate, final String typeName,
			final Map<String, String> locationMap, final Map<String, Integer> divMap) {
		// 取指标在报表里的行列位置
		dao.run(new ConnCallback() {
			@Override
			public void invoke(Connection conn) throws Exception {

				PreparedStatement stmt = null;
				try {
					String sql = "SELECT rtip.report_type_id,rtip.indicator_id,rtip.row_index,rtip.the_column,ti.name,ti.unit_magnitude "
							+ " FROM t_report_type_indicator rtip"
							+ " INNER JOIN tbl_report_type rt ON rtip.report_type_id = rt.id"
							+ " INNER JOIN tbl_report_version rv ON rt.version_id = rv.id"
							+ " INNER JOIN t_indicator ti on rtip.indicator_id = ti.ID"
							+ " where rv.start_date <= ? AND rv.end_date >= ? AND rt.type_name like ?";
					stmt = conn.prepareStatement(sql);
					stmt.setDate(1, new java.sql.Date(versionDate.getTime()));
					stmt.setDate(2, new java.sql.Date(versionDate.getTime()));
					stmt.setString(3, typeName + "%"); // 模糊查询，有的报表名可能有差异
					ResultSet rs = stmt.executeQuery();
					while (rs.next()) {
						// Key:山东省渔业生产基本情况_海水养殖_数量_年报
						// Value:山东省渔业生产基本情况_0_5
						String location = typeName + "＿" + rs.getInt("row_index") + "_" + rs.getInt("the_column");
						locationMap.put(rs.getString("name"), location);

						// 数值倍数
						String divNum = rs.getString("unit_magnitude"); // 数值的单位，比如1000，10000什么的。
						if (divNum == null) {
							divMap.put(rs.getString("name"), 1);
						} else {
							divMap.put(rs.getString("name"), Double.valueOf(divNum).intValue());
						}
					}
				} finally {
					if (null != stmt) {
						stmt.close();
					}
				}
			}
		});
	}

	/**
	 * 从DB中取得指标的值
	 * 
	 * @param timeVo
	 * @param typeName
	 * @param valueMap
	 */
	private void getIndValueFromDB(final ReportTimeVO timeVo, final String typeName, final Map<String, String> valueMap) {
		dao.run(new ConnCallback() {
			@Override
			public void invoke(Connection conn) throws Exception {

				PreparedStatement stmt = null;
				try {
					UnitNz unitP = dao.fetch(
							UnitNz.class,
							Cnd.where("unitRoleId", "=", ReportConstant.OCEAN).and("unitAreaId", "=",
									ReportConstant.PROVINCE_AREA_ID));
					String sql = "SELECT rd.* FROM tbl_report r inner JOIN tbl_report_detail  rd on r.id = rd.report_id"
							+ " WHERE r.report_type =? AND r.report_area_id='"
							+ ReportConstant.PROVINCE_AREA_ID
							+ "' AND r.unit_id = '" + unitP.getId() + "' ";

					// 汇总时间频率条件SQL
					if (timeVo.timeType == Constants.REPORT_TIME_MONTH) {
						// 月报
						sql += " AND r.report_timetype = ? AND r.report_year = ? AND r.month = ? ";
						stmt = conn.prepareStatement(sql);
						stmt.setString(1, typeName);
						stmt.setString(2, "" + timeVo.getTimeType());
						stmt.setString(3, "" + timeVo.getYear());
						stmt.setString(4, "" + timeVo.getMonth());
					} else if (timeVo.timeType == Constants.REPORT_TIME_YEAR) {
						// 年报，半年报
						sql += " and r.report_timetype =? and r.report_year_time =? AND r.report_year =? ";
						stmt = conn.prepareStatement(sql);
						stmt.setString(1, typeName);
						stmt.setString(2, "" + timeVo.getTimeType());
						stmt.setString(3, "" + timeVo.getYearTime()); // 年报或半年报
						stmt.setString(4, "" + timeVo.getYear()); // 年份
					} else {
						// 季报
						sql += " AND r.report_timetype = ? AND r.report_year = ? AND r.quarter = ? ";
						stmt = conn.prepareStatement(sql);
						stmt.setString(1, typeName);
						stmt.setString(2, "" + timeVo.getTimeType());
						stmt.setString(3, "" + timeVo.getYear());
						stmt.setString(4, "" + timeVo.getQuarter());
					}

					ResultSet rs = stmt.executeQuery();
					while (rs.next()) {

						// Value:山东省渔业生产基本情况_0_5
						// Value:xxxx
						for (int i = 1; i <= 60; i++) {
							String key = typeName + "＿" + rs.getString("row_index") + "_" + i;
							valueMap.put(key, rs.getString("f" + i));
						}

					}
				} finally {
					if (null != stmt) {
						stmt.close();
					}
				}
			}
		});
	}

	/**
	 * 取得word模板名称.
	 * 
	 * @param timeType
	 *            报表类型
	 * @return
	 */
	public String getWordTemplateFileName(int timeType) {
		String fileName = "";
		if (timeType == Constants.REPORT_TIME_HALF_YEAR) {
			fileName = "渔业经济形势分析模板（半年报）.doc";
		} else if (timeType == Constants.REPORT_TIME_YEAR) {
			fileName = "渔业经济形势分析模板（年报）.doc";
		} else if (timeType == Constants.REPORT_TIME_MONTH) {
			fileName = "渔业经济形势分析模板（月报）.doc";
		} else {
			fileName = "渔业经济形势分析模板（季报）.doc";
		}
		return fileName;
	}

	/**
	 * 报表时间值对象.
	 */
	public static class ReportTimeVO {
		Integer timeType;
		Integer year;
		Integer yearTime;
		Integer quarter;
		Integer month;

		public ReportTimeVO() {
		}

		public ReportTimeVO(Integer timeType, Integer year, Integer yearTime, Integer quarter, Integer month) {
			this.timeType = timeType;
			this.year = year;
			this.yearTime = yearTime;
			this.quarter = quarter;
			this.month = month;
		}

		public static ReportTimeVO create(Integer timeType, Integer year, Integer yearTime, Integer quarter,
				Integer month) {
			ReportTimeVO rtv = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				rtv = createYearTime(timeType, year, yearTime);
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				rtv = createQuarterTime(timeType, year, quarter);
			} else if (timeType == Constants.REPORT_TIME_MONTH) {
				rtv = createMonthTime(timeType, year, month);
			} else {
				throw Lang.makeThrow("时间类型错误，无法创建,时间类型:%d", timeType);
			}
			return rtv;
		}

		public static ReportTimeVO createYearTime(Integer timeType, Integer year, Integer yearTime) {
			return new ReportTimeVO(timeType, year, yearTime, null, null);
		}

		public static ReportTimeVO createYearTime(Integer year, Integer yearTime) {
			return new ReportTimeVO(Constants.REPORT_TIME_YEAR, year, yearTime, null, null);
		}

		public static ReportTimeVO createQuarterTime(Integer timeType, Integer year, Integer quarter) {
			return new ReportTimeVO(timeType, year, null, quarter, null);
		}

		public static ReportTimeVO createQuarterTime(Integer year, Integer quarter) {
			return new ReportTimeVO(Constants.REPORT_TIME_QUARTER, year, null, quarter, null);
		}

		public static ReportTimeVO createMonthTime(Integer timeType, Integer year, Integer month) {
			return new ReportTimeVO(timeType, year, null, null, month);
		}

		public static ReportTimeVO createMonthTime(Integer year, Integer month) {
			return new ReportTimeVO(Constants.REPORT_TIME_MONTH, year, null, null, month);
		}

		@Override
		public ReportTimeVO clone() {
			ReportTimeVO one = new ReportTimeVO();
			if (this.timeType != null) {
				one.timeType = this.timeType.intValue();
			}
			if (this.year != null) {
				one.year = this.year.intValue();
			}
			if (this.yearTime != null) {
				one.yearTime = this.yearTime.intValue();
			}
			if (this.quarter != null) {
				one.quarter = this.quarter.intValue();
			}
			if (this.month != null) {
				one.month = this.month.intValue();
			}

			return one;
		}

		public String format() {
			String str = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				if (yearTime == 1) {
					str = String.format("%d年 上半年", year);
				} else if (yearTime == 2) {
					str = String.format("%d年", year);
				} else {
					str = String.format("%d年 快报", year);
				}
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				str = String.format("%d年%d季度", year, quarter);
			} else {
				str = String.format("%d年%d月", year, month);
			}
			return str;
		}

		/**
		 * 将报表报送时间转为报表版本的日期.
		 * 
		 * @return 返回报表版本日期.
		 */
		public Date toReportVersionDate() {
			Date date = null;
			if (timeType == Constants.REPORT_TIME_YEAR) {
				if (yearTime == 1) {
					date = Times.D(String.format("%d-06-30", year));
				} else if (yearTime == 2) {
					date = Times.D(String.format("%d-12-31", year));
				} else {
					throw Lang.makeThrow(String.format("年报类型错误，year:%d,yearTime:%d", year, yearTime));
				}
			} else if (timeType == Constants.REPORT_TIME_QUARTER) {
				int quarterMonth = quarter * 3;
				date = Times.D(String.format("%d-%02d-01", year, quarterMonth));
			} else if (timeType == Constants.REPORT_TIME_MONTH) {
				date = Times.D(String.format("%d-%02d-01", year, month));
			} else {
				throw Lang.makeThrow(String.format("时间类型错误，timeType:%d", timeType));
			}
			return date;
		}

		@Override
		public String toString() {
			return "ReportTimeVO [timeType=" + timeType + ", year=" + year + ", yearTime=" + yearTime + ", quarter="
					+ quarter + ", month=" + month + "]";
		}

		public Integer getTimeType() {
			return timeType;
		}

		public void setTimeType(Integer timeType) {
			this.timeType = timeType;
		}

		public Integer getYear() {
			return year;
		}

		public void setYear(Integer year) {
			this.year = year;
		}

		public Integer getYearTime() {
			return yearTime;
		}

		public void setYearTime(Integer yearTime) {
			this.yearTime = yearTime;
		}

		public Integer getQuarter() {
			return quarter;
		}

		public void setQuarter(Integer quarter) {
			this.quarter = quarter;
		}

		public Integer getMonth() {
			return month;
		}

		public void setMonth(Integer month) {
			this.month = month;
		}

	}

}
